﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.ComponentModel;
using Portal.Core.DAL;
using Portal.BO.Editoral.Newsedit;

namespace Portal.BO.Editoral.CommentManagement
{
	public class CommentManagementHelper
	{

		public CommentManagementHelper() { }

		[DataObjectMethod(DataObjectMethodType.Select)]
		public DataTable ListComment(string strWhere, int PageSize, int StartRow, bool isFistPage)
		{
			//Lay gia tri mode list de xu ly
			if (strWhere == null)
				strWhere = "";
			if (isFistPage)
			{
				StartRow = 0;
			}


			using (MainDB objdb = new MainDB())
			{
				//objresult = objdb.NewsCollection.GetPageAsDataTable(intPageNum, PageSize, strWhere, "News_ModifiedDate DESC"); ;
				return objdb.StoredProcedures.vcc_AdminSearchCommentByKeyword(strWhere, StartRow.ToString(), PageSize.ToString());
			}

		}

		public static DataTable ListComment2(string comment, int type, int PageSize, int StartRow, string sortExpression)
		{
			return ListComment2(comment, type, -1, PageSize, StartRow, sortExpression);
		}
		public static DataTable ListComment2(string comment, int type, int catID, int PageSize, int StartRow, string sortExpression)
		{
			return ListComment2(comment, string.Empty, type, catID, PageSize, StartRow, sortExpression);
		}
		public static DataTable ListComment2(string comment, string newsTitle, int type, int catID, int PageSize, int StartRow, string sortExpression)
		{
			if (!string.IsNullOrEmpty(comment))
			{
				comment = comment.Replace("%", string.Empty);
				comment = comment.Replace("'", string.Empty);
				comment = comment.Replace(";", string.Empty);
				comment = comment.Replace("--", string.Empty);
				comment = "%" + comment + "%";
			}
			if (!string.IsNullOrEmpty(newsTitle))
			{
				newsTitle = newsTitle.Replace("%", string.Empty);
				newsTitle = newsTitle.Replace("'", string.Empty);
				newsTitle = newsTitle.Replace(";", string.Empty);
				newsTitle = newsTitle.Replace("--", string.Empty);
				newsTitle = "%" + newsTitle + "%";
			}

			string sqlWhere = string.Empty;
			if (type == 0)
				sqlWhere = " Approver IS NULL ";
			else
				sqlWhere = " Approver IS NOT NULL ";

			
			if (catID >= 1)
			{
				sqlWhere += " And News.Cat_ID = " + catID;
			}

			if (!string.IsNullOrEmpty(comment)) sqlWhere += " And (Comment_Content LIKE @comment)";
			if (!string.IsNullOrEmpty(newsTitle)) sqlWhere += " And (News_Title LIKE @newsTitle)";

			string sql = @"	SELECT Comment.*, row_number() OVER (ORDER BY {0}) DocNum, News_Title
							FROM Comment
							Left Join
							News On Comment.News_ID = News.News_ID
							WHERE " + sqlWhere;
			sql = string.Format(sql, sortExpression);

			sql = "Select * From (" + sql + ") as TemTable WHERE DocNum BETWEEN " + StartRow + " AND " + (StartRow + PageSize - 1);

			using (MainDB objdb = new MainDB())
			{
				if (string.IsNullOrEmpty(comment))
				{
					if (string.IsNullOrEmpty(newsTitle))
						return objdb.SelectQuery(sql);
					else
						return objdb.SelectQuery(sql, new object[] { newsTitle }, new string[] { "@newsTitle" });
				}
				else
				{
					if (string.IsNullOrEmpty(newsTitle))
						return objdb.SelectQuery(sql, new object[] { comment }, new string[] { "@comment" });
					else
						return objdb.SelectQuery(sql, new object[] { comment, newsTitle }, new string[] { "@comment", "@newsTitle" });
				}
			}

		}

		public static int Count(string keyword, int type)
		{
			return Count(keyword, type, -1);
		}
		public static int Count(string keyword, int type, int catId)
		{
			return Count(keyword, type, catId, string.Empty);
		}
		public static int Count(string comment, int type, int catId,string newsTitle)
		{
			if (!string.IsNullOrEmpty(comment))
			{
				comment = comment.Replace("%", string.Empty);
				comment = comment.Replace("'", string.Empty);
				comment = comment.Replace(";", string.Empty);
				comment = comment.Replace("--", string.Empty);
				comment = "%" + comment + "%";
			}
			if (!string.IsNullOrEmpty(newsTitle))
			{
				newsTitle = newsTitle.Replace("%", string.Empty);
				newsTitle = newsTitle.Replace("'", string.Empty);
				newsTitle = newsTitle.Replace(";", string.Empty);
				newsTitle = newsTitle.Replace("--", string.Empty);
				newsTitle = "%" + newsTitle + "%";
			}

			string sqlWhere = "Comment_Approved = " + type;
			if (type == 0)
			{
				// Neu la Comment Chua duyet thi lay ca nhung comment co Comment_Approved = 1 va Approver Is Null
				sqlWhere = " (Approver Is Null ) ";
			}
			else
				if (type == 1)
				{
					sqlWhere = " (Approver Is not Null) ";
				}
			if (catId >= 1)
			{
				sqlWhere += " And News.Cat_ID = " + catId;
			}

			if (!string.IsNullOrEmpty(comment)) sqlWhere += " And (Comment_Content LIKE @comment)";
			if (!string.IsNullOrEmpty(newsTitle)) sqlWhere += " And (News_Title LIKE @newsTitle)";

			string sql = @"	SELECT Count(Comment.Comment_ID)
							FROM Comment
							Left Join
							News On Comment.News_ID = News.News_ID
							WHERE " + sqlWhere;

			using (MainDB objdb = new MainDB())
			{
				if (string.IsNullOrEmpty(comment))
				{
					if (string.IsNullOrEmpty(newsTitle))
						return (int)objdb.SelectScalar(sql);
					else
						return (int)objdb.SelectScalar(sql, new object[] { newsTitle }, new string[] { "@newsTitle" });
				}
				else
				{
					if (string.IsNullOrEmpty(newsTitle))
						return (int)objdb.SelectScalar(sql, new object[] { comment }, new string[] { "@comment" });
					else
						return (int)objdb.SelectScalar(sql, new object[] { comment, newsTitle }, new string[] { "@comment", "@newsTitle" });
				}
			}

		}

		[DataObjectMethod(DataObjectMethodType.Select)]
		public static DataTable GetListComment(string news_id, string viewtype, string keyword)
		{
			string strWhere = "";
			if (keyword != null && keyword != "")
			{
				keyword = keyword.Trim().Replace("'", "");
				strWhere = " AND (Comment_Content like N'%" + keyword + "%' OR Approver like '%" + keyword + "%' OR Comment_User like N'%" + keyword + "%'  )";
			}
			using (MainDB objdb = new MainDB())
			{
				if (viewtype == "0")
					//return objdb.SelectQuery(" Select * from Comment Where   news_id = '" + news_id + "' And Comment_Approved = 0 " + strWhere + " order by Comment_ID DESC ");
					return objdb.StoredProcedures.vc_Execute_Sql(" Select * from Comment Where   news_id = '" + news_id + "' And Comment_Approved = 0 " + strWhere + " order by Comment_ID DESC ");
				else
					//return objdb.SelectQuery(" Select * from Comment Where   news_id = '" + news_id + "' And Comment_Approved = 1 " + strWhere + " order by Comment_ID DESC ");
					return objdb.StoredProcedures.vc_Execute_Sql(" Select * from Comment Where   news_id = '" + news_id + "' And Comment_Approved = 1 " + strWhere + " order by Comment_ID DESC ");
			}
		}

		/// <summary>
		/// Xóa tất cả các danh mục được chọn
		/// </summary>
		/// <param name="_selected_id"></param>
		[DataObjectMethod(DataObjectMethodType.Delete)]
		public static void DeleteAllSelectedComment(string _selected_id)
		{
			string[] commentIds = _selected_id.Split(",".ToCharArray());
			foreach (string commentid in commentIds)
			{
				DelCatRow(commentid);
			}
		}
		private static void DelCatRow(string _comment_id)
		{
			using (MainDB objDb = new MainDB())
			{
				objDb.StoredProcedures.vcc_DeleteCommentByID(_comment_id);
			}
		}

		public static void DeleteMultiple(string commentIds)
		{
			using (MainDB objDb = new MainDB())
			{
				objDb.SelectQuery("Delete From Comment Where Comment_ID In (" + commentIds + ")");
			}
		}

		public static void SetApprove(string commentIds, bool value)
		{
			using (MainDB objDb = new MainDB())
			{
				objDb.SelectQuery("Update Comment Set Comment_Approved = " + (value ? 1 : 0) + ", Approver = '" + HttpContext.Current.User.Identity.Name + "' " + " Where Comment_ID In (" + commentIds + ")");
			}
		}

		[DataObjectMethod(DataObjectMethodType.Update)]
		public void UpdateComment(string _command_name, string _command_argument, string _edit_commentcontent)
		{
			switch (_command_name)
			{
				case "Approve":
					{
						// Cap nhat lai bang Comment va set Comment_Approved = True
						using (MainDB objDb = new MainDB())
						{
							objDb.StoredProcedures.vcc_UpdateCommentByID(_command_argument);
						}
					}
					break;
				case "Update":
					{
						using (MainDB objDb = new MainDB())
						{
							objDb.StoredProcedures.vcc_UpdateCommentByStaff(_edit_commentcontent, _command_argument);
						}
					}
					break;
				case "DisApprove":
					{
						using (MainDB objDb = new MainDB())
						{
							objDb.SelectQuery(" Update Comment Set Comment_Approved = 0 Where Comment_ID = " + _command_argument);
						}
					}
					break;
				default:
					break;
			}
		}


		public static int GetRowsCount(string strWhere, bool isFistPage)
		{
			if (strWhere == null)
				strWhere = "";
			using (MainDB objdb = new MainDB())
			{
				return (int)objdb.StoredProcedures.vcc_CountAdminSearchCommentByKeyword(strWhere).Rows[0][0];
			}

		}

		//Chua chuyen sang store
		public static CommentRow GetCommentByID(long commentid)
		{
			using (MainDB objDB = new MainDB())
			{
				return objDB.CommentCollection.GetByPrimaryKey(commentid);
			}
		}

		//Chua chuyen sang store
		[DataObjectMethod(DataObjectMethodType.Update)]
		public static void Update(long _comment_id, string _user_send, string _email, string _content)
		{
			CommentRow objComment;
			using (MainDB objDB = new MainDB())
			{
				objComment = objDB.CommentCollection.GetByPrimaryKey(_comment_id);
			}

			if (objComment != null)
			{
				objComment.Comment_Content = NewsEditHelper.ReplaceEmoticonToImageSrc(_content);
				objComment.Comment_Email = _email;
				objComment.Comment_User = _user_send;

				using (MainDB objDB = new MainDB())
				{
					objDB.CommentCollection.Update(objComment);
				}
			}
		}

		[DataObjectMethod(DataObjectMethodType.Select)]
		public static DataTable GetListNewsComment(string strWhere, int PageSize, int StartRow, string view_type)
		{
			//Lay gia tri mode list de xu ly
			if (strWhere == null)
				strWhere = "";

			DataTable objresult = new DataTable();

			int end_index = StartRow + PageSize;

			if (view_type.ToLower() == "baivietcocomment")
			{
				// Hien thi tat ca
				//lay duoc du lieu cua tat ca nhung thang co trang thai la status can lay

				int intPageNum = StartRow / PageSize + 1;
				using (MainDB objdb = new MainDB())
				{
					objresult = objdb.StoredProcedures.Comment_GetCommentRead(strWhere, StartRow.ToString(), end_index.ToString());
				}
				if (objresult.Rows.Count == 0)
					return objresult;
			}
			else if (view_type.ToLower() == "baivietmoi")
			{

				// Hien thi tat ca
				//lay duoc du lieu cua tat ca nhung thang co trang thai la status can lay
				using (MainDB objdb = new MainDB())
				{
					objresult = objdb.StoredProcedures.Comment_GetCommentNotRead(strWhere, StartRow.ToString(), end_index.ToString());
				}
				if (objresult.Rows.Count == 0)
					return objresult;
			}



			objresult.Columns.Add(new DataColumn("CommentNotRead"));
			objresult.Columns.Add(new DataColumn("ViewCount"));
			objresult.Columns.Add(new DataColumn("Cat_Name"));
			objresult.Columns.Add(new DataColumn("ModifyDate"));
			DataTable objTemp = objresult.Clone();
			object[] objarr = null;


			MainDB _db = new MainDB();
			foreach (DataRow objrow in objresult.Rows)
			{
				objarr = objrow.ItemArray;
				objarr[objarr.Length - 4] = GetCommentNotReadCount(objrow[0].ToString());
				objarr[objarr.Length - 3] = GetCommentCount(objrow[0].ToString());
				objarr[objarr.Length - 2] = _db.CategoryCollection.GetByPrimaryKey(Convert.ToInt32(objrow["cat_id"])).Cat_Name;
				objarr[objarr.Length - 1] = Convert.ToDateTime(objrow["News_ModifiedDate"]).ToString("dd/MM/yyyy:h:m");
				objTemp.Rows.Add(objarr);
			}

			_db.Dispose();
			objresult.Dispose();
			return objTemp;
		}

		public static int GetRowCount(string strWhere, string view_type)
		{
			if (strWhere == null)
				strWhere = "";

			if (view_type.ToLower() == "baivietcocomment")
			{
				using (MainDB objdb = new MainDB())
				{
					return Convert.ToInt32(objdb.StoredProcedures.Comment_CountGetCommentRead(strWhere).Rows[0][0]);
				}
			}
			else
			{
				using (MainDB objdb = new MainDB())
				{
					return Convert.ToInt32(objdb.StoredProcedures.Comment_CountGetCommentNotRead(strWhere).Rows[0][0]);
				}
			}

		}

		public static string CountComment()
		{
			using (MainDB objdb = new MainDB())
			{
				return objdb.StoredProcedures.CountComment().Rows[0][0].ToString();
			}
		}

		public static string CountAllCommentThaoLuan()
		{
			using (MainDB objdb = new MainDB())
			{
				return objdb.StoredProcedures.CountAllCommentThaoLuan().Rows[0][0].ToString();
			}
		}

		public static string CountAllComment()
		{
			using (MainDB objdb = new MainDB())
			{
				return objdb.StoredProcedures.CountAllComment().Rows[0][0].ToString();
			}
		}

		//Da chuyen sang store
		public static string GetCommentCount(string news_id)
		{

			using (MainDB _db = new MainDB())
			{
				//return _db.CommentCollection.GetCount(" News_Id = " + news_id).ToString();
				DataTable table = _db.StoredProcedures.vc_Execute_Sql("SELECT COUNT(Comment_ID) FROM Comment WHERE News_ID = " + news_id);
				if (table.Rows.Count != 0)
					return table.Rows[0][0].ToString();
				else
					return String.Empty;
			}

		}

		//Chua chuyen sang store
		public static string GetCommentNotReadCount(string news_id)
		{

			using (MainDB _db = new MainDB())
			{
				return _db.CommentCollection.GetCount(" News_Id = " + news_id + " AND Comment_Approved = 0 ").ToString();
				//DataTable table = _db.StoredProcedures.vc_Execute_Sql(" News_Id = " + news_id + " AND Comment_Approved = 0 ");
			}

		}


		public static void ApproveCommentByNewsId(string news_id, string strComment)
		{
			using (MainDB objDB = new MainDB())
			{
				//objDB.SelectQuery(" Update Comment Set Comment_Approved = 1, Approver = '" + HttpContext.Current.User.Identity.Name + "' Where News_ID = " + news_id + " AND Comment_ID IN (" + strComment + ") ");
				objDB.StoredProcedures.vc_Execute_Sql(" Update Comment Set Comment_Approved = 1, Approver = '" + HttpContext.Current.User.Identity.Name + "' Where News_ID = " + news_id + " AND Comment_ID IN (" + strComment + ") ");
			}
		}




		[DataObjectMethod(DataObjectMethodType.Select)]
		public static DataTable GetListNewsCommentThaoLuan(string strWhere, int PageSize, int StartRow, string view_type)
		{
			//Lay gia tri mode list de xu ly
			if (strWhere == null)
				strWhere = "";

			DataTable objresult = new DataTable();

			int end_index = StartRow + PageSize;

			if (view_type.ToLower() == "baivietcocomment")
			{
				// Hien thi tat ca
				//lay duoc du lieu cua tat ca nhung thang co trang thai la status can lay

				int intPageNum = StartRow / PageSize + 1;
				using (MainDB objdb = new MainDB())
				{
					objresult = objdb.StoredProcedures.CommentFeedBackNews_GetCommentRead(strWhere, StartRow.ToString(), end_index.ToString());
				}
				if (objresult.Rows.Count == 0)
					return objresult;
			}
			else if (view_type.ToLower() == "baivietmoi")
			{

				// Hien thi tat ca
				//lay duoc du lieu cua tat ca nhung thang co trang thai la status can lay
				using (MainDB objdb = new MainDB())
				{
					objresult = objdb.StoredProcedures.CommentFeedBackNews_GetCommentNotRead(strWhere, StartRow.ToString(), end_index.ToString());
				}
				if (objresult.Rows.Count == 0)
					return objresult;
			}



			objresult.Columns.Add(new DataColumn("CommentNotRead"));
			objresult.Columns.Add(new DataColumn("ViewCount"));
			objresult.Columns.Add(new DataColumn("Cat_Name"));
			objresult.Columns.Add(new DataColumn("ModifyDate"));
			DataTable objTemp = objresult.Clone();
			object[] objarr = null;


			MainDB _db = new MainDB();
			foreach (DataRow objrow in objresult.Rows)
			{
				objarr = objrow.ItemArray;
				objarr[objarr.Length - 4] = GetCommentThaoLuanNotReadCount(Convert.ToInt32(objrow[0]));
				objarr[objarr.Length - 3] = GetCommentThaoLuanCount(Convert.ToInt32(objrow[0]));
				objarr[objarr.Length - 2] = _db.CategoryCollection.GetByPrimaryKey(Convert.ToInt32(objrow["cat_id"])).Cat_Name;
				objarr[objarr.Length - 1] = objrow["News_ModifedDate"] != DBNull.Value ? Convert.ToDateTime(objrow["News_ModifedDate"]).ToString("dd/MM/yyyy:h:m") : "";
				objTemp.Rows.Add(objarr);
			}

			_db.Dispose();
			objresult.Dispose();
			return objTemp;
		}


		public static int GetRowCountThaoLuan(string strWhere, string view_type)
		{
			if (strWhere == null)
				strWhere = "";


			if (view_type.ToLower() == "baivietcocomment")
			{
				using (MainDB objdb = new MainDB())
				{
					return Convert.ToInt32(objdb.StoredProcedures.CommentFeedBackNews_CountGetCommentRead(strWhere).Rows[0][0]);
				}
			}
			else
			{
				using (MainDB objdb = new MainDB())
				{
					return Convert.ToInt32(objdb.StoredProcedures.CommentFeedBackNews_CountGetCommentNotRead(strWhere).Rows[0][0]);
				}
			}

		}


		public static string GetCommentThaoLuanNotReadCount(int feedback_id)
		{

			using (MainDB _db = new MainDB())
			{
				return _db.StoredProcedures.GetCommentThaoLuanNotReadCount(feedback_id).Rows[0][0].ToString();
			}

		}


		public static string GetCommentThaoLuanCount(int feedback_id)
		{

			using (MainDB _db = new MainDB())
			{
				return _db.StoredProcedures.GetCommentThaoLuanCount(feedback_id).Rows[0][0].ToString();
			}

		}



		[DataObjectMethod(DataObjectMethodType.Select)]
		public static DataTable GetListCommentThaoLuan(string viewtype, string keyword, int PageSize, int StartRow)
		{
			string strWhere = "";
			if (keyword != null && keyword != "")
			{
                keyword = keyword.Trim().Replace("'", ""); 
                strWhere = " AND (Comment_Content like N'%" + keyword + "%' OR Approver like '%" + keyword + "%' OR Comment_User like N'%" + keyword + "%' OR News_Title like N'%"+ keyword +"%'  )";
			}

			int end_index = StartRow + PageSize;

			using (MainDB objdb = new MainDB())
			{
				return objdb.StoredProcedures.CommentThaoLuan_GetList(strWhere, viewtype, StartRow.ToString(), end_index.ToString());
			}
		}

		public static int CommentThaoLuan_Count(string viewtype, string keyword)
		{
			string strWhere = "";
			if (keyword != null && keyword != "")
			{
				keyword = keyword.Trim().Replace("'", "");
                strWhere = " AND (Comment_Content like N'%" + keyword + "%' OR Approver like '%" + keyword + "%' OR Comment_User like N'%" + keyword + "%' OR News_Title like N'%" + keyword + "%'  )";
			}

			using (MainDB objdb = new MainDB())
			{
				return Convert.ToInt32(objdb.StoredProcedures.CommentThaoLuan_Count(strWhere, viewtype).Rows[0][0]);
			}
		}

		public static void ApproveCommentThaoLuanByFeedBackId(string strComment)
		{
			using (MainDB objDB = new MainDB())
			{
				objDB.StoredProcedures.vc_Execute_Sql(" Update Comment_FeedBackNews Set Comment_Approved = 1, Approver = '" + HttpContext.Current.User.Identity.Name + "' Where Comment_ID IN (" + strComment + ") ");
			}
		}

		[DataObjectMethod(DataObjectMethodType.Delete)]
		public static void DeleteAllSelectedCommentThaoLuan(string _selected_id)
		{
			using (MainDB objDB = new MainDB())
			{
				objDB.StoredProcedures.vc_Execute_Sql(" Delete Comment_FeedBackNews  Where Comment_ID IN (" + _selected_id + ") ");
			}
		}

		public static void Comment_FeedBackNewsUpdate(Int64 Comment_ID, Int64 FeedBack_ID, Decimal Comment_Rate, DateTime Comment_Date, String Comment_Content, String Comment_User, Boolean Comment_Approved, String Comment_Email, String Approver)
		{
			using (MainDB objDB = new MainDB())
			{
				objDB.StoredProcedures.Comment_FeedBackNewsUpdate(Comment_ID, FeedBack_ID, Comment_Rate, Comment_Date, Comment_Content, Comment_User, Comment_Approved, Comment_Email, Approver);
			}
		}

		public static DataTable CommentFeedBackNews_GetInfo(string Comment_ID)
		{
			using (MainDB objDB = new MainDB())
			{
				return objDB.SelectQuery("Select * from Comment_FeedBackNews Where Comment_ID = " + Comment_ID);
			}
		}
		public static string CountCommentFeedbacknews()
		{
			using (MainDB objDB = new MainDB())
			{
				return objDB.StoredProcedures.CountCommentFeedbacknews().Rows[0][0].ToString();
			}
		}


	}
}
